﻿using DXApplicationTangche.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DXApplicationTangche.UC.报表.service
{
    class 抖音报表service
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable get备货()
        {
            String sql = "-- 抖音备货中\n" +
                "select\n" +
                "suborderno, -- 子订单号 spec, -- 规格\n" +
                "stylecode, -- 款号\n" +
                "materialcode, --  面料号\n" +
                "stylesizecd, -- 尺码\n" +
                "quantity, -- 订单数量\n" +
                "unitprice, -- 单价\n" +
                "discount, --  折扣\n" +
                "payable, -- 订单总金额\n" +
                "orderdate, -- 订单详细时间\n" +
                "orderday, -- 订单日期\n" +
                "orderstatus, -- 订单状态\n" +
                "customername, -- 客户名\n" +
                "mobile, -- 电话\n" +
                "addressprovince, -- 省\n" +
                "addresscity, -- 市\n" +
                "addressdistrict, -- 县\n" +
                "addressdetail -- 详细地址\n" +
                "from\n" +
                "t_douyin_preparation_details\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        internal static object get抖音客户订单()
        {
            throw new NotImplementedException();
        }

        public static DataTable get已发货未到账()
        {
            String sql = "-- 已发货未到账\n" +
                        "select\n" +
                        "suborderno,\t-- 子订单号\n" +
                        "spec, -- 规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, -- 订单详细时间\n" +
                        "orderday, -- 订单日期\n" +
                        "orderstatus, -- 订单状态\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince, -- 省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict, -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyin_delivery_but_not_received_details;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get每日发货明细()
        {
            String sql = "-- 每日发货明细\n" +
                        "select\n" +
                        "suborderno, -- 子订单号\n" +
                        "spec, -- 规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, -- 订单详细时间\n" +
                        "orderstatus, -- 订单状态\n" +
                        "deliverydate, -- 交货日期\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince,  -- 省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict,  -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyin_daily_delivery_details;\n";;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get每日收款到账()
        {
            String sql = "-- 每日收款到账\n" +
                        "select\n" +
                        "suborderno, -- 子订单号\n" +
                        "spec, --规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, -- 订单详细时间\n" +
                        "orderday, -- 订购日\n" +
                        "orderstatus, -- 订单状态\n" +
                        "ordercompletedate, -- 订单完成\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince, -- 省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict, -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyn_daily_receipt_details;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get每日未发货退货()
        {
            String sql = "-- 每日未发货退货\n" +
                        "select\n" +
                        "suborderno, -- 子订单号\n" +
                        "spec, -- 规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, --尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, -- 订单详细时间\n" +
                        "orderstatus, -- 订单状态\n" +
                        "aftersaletype, -- 售后服务类型\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince, -- 省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict, -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyin_daily_undelivered_return_details;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get每日直播间成交明细()
        {
            String sql = "-- 每日直播间成交明细\n" +
                        "select\n" +
                        "suborderno, -- 子订单号\n" +
                        "spec, -- 规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, --订单详细时间\n" +
                        "orderday, -- 日期\n" +
                        "orderstatus, -- 订单状态\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince, -- 省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict, -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyin_daily_zhibo_order_details;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get每日销售全部订单()
        {
            String sql = "-- 每日销售全部订单\n" +
                        "select\n" +
                        "suborderno, -- 子订单号\n" +
                        "spec, -- 规格\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity, -- 订单数量\n" +
                        "unitprice, -- 单价\n" +
                        "discount, -- 折扣\n" +
                        "payable, -- 订单总金额\n" +
                        "orderdate, -- 订单详细时间\n" +
                        "orderday, -- 订单日期\n" +
                        "orderstatus, -- 订单状态\n" +
                        "customername, -- 客户名\n" +
                        "mobile, -- 电话\n" +
                        "addressprovince, --省\n" +
                        "addresscity, -- 市\n" +
                        "addressdistrict, -- 县\n" +
                        "addressdetail -- 详细地址\n" +
                        "from\n" +
                        "t_douyin_daily_sales_order_details;\n"; ;
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        /// <summary>
        /// 库存查询
        /// </summary>
        /// <returns></returns>
        public static DataTable getStock()
        {
            String sql = "select\n" +
                        "stylecode,\n" +
                        "stylename,\n" +
                        "fabriccode,\n" +
                        "fabricname,\n" +
                        "stylesizecd,\n" +
                        "storeid,\n" +
                        "storename,\n" +
                        "count(barcode) as quantity\n" +
                        "from\n" +
                        "t_barcode_in_stock tbis\n" +
                        "group by\n" +
                        "stylecode ,\n" +
                        "stylename ,\n" +
                        "fabriccode ,\n" +
                        "fabricname,\n" +
                        "stylesizecd ,\n" +
                        "storeid ,\n" +
                        "storename;\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get客户订单(String mobile)
        {
            String sql = "select\n" +
                "suborderno, -- 子订单号\n" +
                "inventoryno, -- 库存号\n" +
                "spec, -- 规格\n" +
                "stylecode, -- 款号\n" +
                "materialcode, -- 面料号\n" +
                "stylesizecd, -- 尺码\n" +
                "quantity, -- 订单数量\n" +
                "orderdate, -- 订单详细时间\n" +
                "orderday, -- 订单日期\n" +
                "refundtime, -- 退款时间\n" +
                "unitprice, -- 单价\n" +
                "discount, -- 折扣\n" +
                "payable, -- 订单总金额\n" +
                "orderstatus, -- 订单状态\n" +
                "additionstatus, -- 附加状态\n" +
                "deliverydate, -- 交货日期\n" +
                "ordercompletedate, -- 订单完成日期\n" +
                "aftersaletype, -- 售后类型\n" +
                "returnorderno, -- 退货订单号\n" +
                "deliverystatus, -- 交货状态\n" +
                "returnexception, -- 返回异常\n" +
                "aftersaleapplytime, -- 售后时间\n" +
                "customername,-- 客户名\n" +
                "mobile, -- 电话\n" +
                "addressprovince, -- 省\n" +
                "addresscity, -- 市\n" +
                "addressdistrict, -- 县\n" +
                "addressdetail --详细地址\n" +
                "from v_douyin_order_with_user_info where mobile='"+ mobile+ "';\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get客户()
        {
            String sql = "select\n" +
                        "order_summary.mobile , -- 电话\n" +
                        "t_douyin_user_address_info.customername , -- 客户名\n" +
                        "t_douyin_user_address_info.addressprovince , -- 省\n" +
                        "t_douyin_user_address_info.addresscity , -- 市\n" +
                        "t_douyin_user_address_info.addressdistrict , -- 县\n" +
                        "t_douyin_user_address_info.addressstreet , --街道\n" +
                        "t_douyin_user_address_info.addressdetail , -- 详细地址\n" +
                        "order_summary.suborderno , -- 子订单号\n" +
                        "order_summary.unitprice , -- 单价\n" +
                        "order_summary.discount , -- 折扣\n" +
                        "order_summary.payable , -- 订单总金额\n" +
                        "order_summary.aftersaleflg, -- 售后服务类型\n" +
                        "order_summary.orderdate  --订单详细时间\n" +
                        "from\n" +
                        "(\n" +
                        "select\n" +
                        "mobile ,\n" +
                        "suborderno ,\n" +
                        "unitprice ,\n" +
                        "discount ,\n" +
                        "payable ,\n" +
                        "case\n" +
                        "when aftersaletype = '退货退款' then 1\n" +
                        "else 0\n" +
                        "end as aftersaleflg,\n" +
                        "orderdate\n" +
                        "from\n" +
                        "v_douyin_order_with_user_info vdowui\n" +
                        "where\n" +
                        "mobile in (\n" +
                        "select\n" +
                        "mobile\n" +
                        "from\n" +
                        "v_douyin_order_with_user_info vdowui\n" +
                        "where\n" +
                        "suborderno in(\n" +
                        "select\n" +
                        "suborderno\n" +
                        "from\n" +
                        "t_douyin_order_deliver\n" +
                        "where\n" +
                        "createdate >'2022-06-18'\n" +
                        ")\n" +
                        ")\n" +
                        "and (aftersaletype <> '未发货仅退款'\n" +
                        "or aftersaletype is null)\n" +
                        ") order_summary\n" +
                        "left join t_douyin_user_address_info\n" +
                        "on\n" +
                        "order_summary.mobile = t_douyin_user_address_info.mobile\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable get抖音客户订单1(String dateStr)
        {
            String sql = "select\n" +
                        "t_douyin_user_address_info.mobile,\n" +
                        "t_douyin_user_address_info.customername,\n" +
                        "--客户名\n" +
                        "count(suborderno) as order_count,\n" +
                        "-- 订单总数\n" +
                        "sum(payable) as payable ,\n" +
                        "-- 总金额\n" +
                        "sum(case when aftersaletype = '退货退款' then 1 else 0 end ) as return_count,\n" +
                        "--退货数量\n" +
                        "t_douyin_user_address_info.addressprovince,\n" +
                        "--省\n" +
                        "t_douyin_user_address_info.addresscity,\n" +
                        "--市\n" +
                        "t_douyin_user_address_info.addressdistrict,\n" +
                        "--县\n" +
                        "t_douyin_user_address_info.addressstreet,\n" +
                        "--街道\n" +
                        "t_douyin_user_address_info.addressdetail,\n" +
                        "--详细地址\n" +
                        "t_douyin_user_address_info.createdate\n" +
                        "--发货日期\n" +
                        "from\n" +
                        "v_douyin_order_with_user_info\n" +
                        "left join t_douyin_user_address_info\n" +
                        "on\n" +
                        "v_douyin_order_with_user_info.mobile = t_douyin_user_address_info.mobile\n" +
                        "where\n" +
                        "t_douyin_user_address_info.mobile in (\n" +
                        "select\n" +
                        "mobile\n" +
                        "from\n" +
                        "v_douyin_order_with_user_info\n" +
                        "where\n" +
                        "suborderno in(\n" +
                        "select\n" +
                        "suborderno\n" +
                        "from\n" +
                        "t_douyin_order_deliver\n" +
                        "where\n" +
                        "createdate >'"+ dateStr + "'\n" +
                        ")\n" +
                        ")\n" +
                        "and (aftersaletype <> '未发货仅退款'\n" +
                        "or aftersaletype is null)\n" +
                        "group by\n" +
                        "t_douyin_user_address_info.mobile;\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }


        public static DataTable get抖音和有赞商品对照表()
        {
            String sql = "select\n" +
                            "distinct\n" +
                            "t_douyin_sku.productname,  --抖音商品名\n" +
                            "v_spu_cost.sharetitle,  --有赞商品名\n" +
                            "v_spu_cost.price,  --有赞零售价\n" +
                            "t_douyin_sku.stylecode, -- 款号\n" +
                            "t_douyin_sku.materialcode, --面料号\n" +
                            "t_douyin_sku.barcode -- 参考码\n" +
                            "from\n" +
                            "(\n" +
                            "select\n" +
                            "distinct productname ,\n" +
                            "barcode,\n" +
                            "stylecode ,\n" +
                            "materialcode\n" +
                            "from\n" +
                            "t_douyin_sku ) as t_douyin_sku\n" +
                            "left join\n" +
                            "(\n" +
                            "select\n" +
                            "distinct v_spu_cost.sharetitle ,\n" +
                            "v_spu_cost.stylecode ,\n" +
                            "v_spu_cost.materialcode ,\n" +
                            "v_spu_cost.price\n" +
                            "from\n" +
                            "v_spu_cost\n" +
                            ") as v_spu_cost\n" +
                            "on\n" +
                            "v_spu_cost.stylecode = t_douyin_sku.stylecode\n" +
                            "and v_spu_cost.materialcode = t_douyin_sku.materialcode\n" +
                            "order by v_spu_cost.sharetitle,t_douyin_sku.stylecode\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }
        public static DataTable getViewSemiCheckExceptionDao()
        {
            String sql = "select\n" +
                            "distinct\n" +
                            "t_douyin_sku.productname, --抖音商品名,\n" +
                            "v_spu_cost.sharetitle, -- 有赞商品名,\n" +
                            "v_spu_cost.price, --有赞零售价,\n" +
                            "t_douyin_sku.stylecode,--款号\n" +
                            "t_douyin_sku.materialcode, --面料号\n" +
                            "t_douyin_sku.barcode, -- 参考码,\n" +
                            "v_spu_cost.imageurl  -- 图片\n" +
                            "from\n" +
                            "(\n" +
                            "select\n" +
                            "distinct productname ,\n" +
                            "barcode,\n" +
                            "stylecode ,\n" +
                            "materialcode\n" +
                            "from\n" +
                            "t_douyin_sku where productid in (select cast(productid as varchar) from t_hot_sale_data) ) as t_douyin_sku\n" +
                            "left join\n" +
                            "(\n" +
                            "select\n" +
                            "distinct v_spu_cost.sharetitle ,\n" +
                            "v_spu_cost.stylecode ,\n" +
                            "v_spu_cost.materialcode ,\n" +
                            "v_spu_cost.price,\n" +
                            "v_spu_cost.imageurl\n" +
                            "from\n" +
                            "v_spu_cost\n" +
                            ") as v_spu_cost\n" +
                            "on\n" +
                            "v_spu_cost.stylecode = t_douyin_sku.stylecode\n" +
                            "and v_spu_cost.materialcode = t_douyin_sku.materialcode\n" +
                            "order by v_spu_cost.sharetitle,t_douyin_sku.stylecode;\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }


        public static DataTable get对照表(String dateStr)
        {
            String sql = "select\n" +
                        "suborderno,\n" +
                        "--子订单号\n" +
                        "inventoryno,\n" +
                        "--库存号\n" +
                        "spec,\n" +
                        "--规格\n" +
                        "stylecode,\n" +
                        "--款号\n" +
                        "materialcode,\n" +
                        "--面料号\n" +
                        "stylesizecd,\n" +
                        "--尺码\n" +
                        "quantity,\n" +
                        "--订单数量\n" +
                        "orderdate,\n" +
                        "--订单详细时间\n" +
                        "orderday,\n" +
                        "--订单日期\n" +
                        "refundtime,\n" +
                        "--退款时间\n" +
                        "unitprice,\n" +
                        "--单价\n" +
                        "discount,\n" +
                        "--折扣\n" +
                        "payable,\n" +
                        "--订单总金额\n" +
                        "orderstatus,\n" +
                        "--订单状态\n" +
                        "additionstatus,\n" +
                        "--附加状态\n" +
                        "deliverydate,\n" +
                        "--交货日期\n" +
                        "ordercompletedate,\n" +
                        "--订单完成日期\n" +
                        "aftersaletype,\n" +
                        "--售后类型\n" +
                        "returnorderno,\n" +
                        "--退货订单号\n" +
                        "deliverystatus,\n" +
                        "--交货状态\n" +
                        "returnexception ,\n" +
                        "--返回异常\n" +
                        "aftersaleapplytime,\n" +
                        "--售后时间\n" +
                        "customername,\n" +
                        "--客户名\n" +
                        "mobile,\n" +
                        "--电话\n" +
                        "addressprovince,\n" +
                        "--省\n" +
                        "addresscity,\n" +
                        "--市\n" +
                        "addressdistrict,\n" +
                        "--县\n" +
                        "addressdetail\n" +
                        "--详细地址\n" +
                        "from\n" +
                        "v_douyin_order_with_user_info\n" +
                        "where\n" +
                        "orderdate >'2022-06-01'\n" +
                        "and stylecode is null\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

        public static DataTable get备货对照表()
        {
            String sql = "select\n" +
                        "inventoryno, -- 子订单号\n" +
                        "stylecode, -- 款号\n" +
                        "materialcode, -- 面料号\n" +
                        "stylesizecd, -- 尺码\n" +
                        "quantity_sum, -- 抖音备货数\n" +
                        "stock_quantity, -- 在库数\n" +
                        "inferior -- 欠品数\n" +
                        "from\n" +
                        "t_douyin_delivery_stock_status\n" +
                        "where\n" +
                        "cast(inferior as integer) <0\n" +
                        "order by\n" +
                        "stylecode,\n" +
                        "materialcode,\n" +
                        "stylesizecd;\n";
            DataTable dt = SQLreport.GetDataTable(
                sql
            );

            return dt;
        }

    }
}
